--    Author    : XUEZHOUYI
--    Name      : ODS.LOAB_MAP_HBASE.HQL
--    Functions : 
--    Purpose   : 
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2019-03-10  XUEZHOUYI       记录有可能重复，为了避免重复记录有同样的MD5需要衍生一列序号来区分
--

INSERT OVERWRITE TABLE ODS.HBASE_LOAB
SELECT
     F_CREATEMD5(TMP.RN,TMP.DATA_DATE,TMP.AREA_NO_ID,TMP.DATA_SRC_ORG,TMP.DATA_ORG_CODE,TMP.CUSTOMER_TYPE,TMP.CUSTOMER_ID,TMP.LPIC,TMP.REGIST_ADD_CODE,TMP.ENTERPRISE_INVESTOR,TMP.ENTERPRISE_SCALE,TMP.LOAN_IOUS_CODE,TMP.PRODUCT_CATEGORY,TMP.LOANS_ACTUALLY,TMP.LENDING_DATE,TMP.LOAN_DUE_DATE,TMP.ROLL_OVER_DATE,TMP.TERM,TMP.LOAN_CUR_TYPE,TMP.LOAN_BALANCE,TMP.RATE_FIXED_FLOAT,TMP.RATE_LEVEL,TMP.GUARANTY_STYLE,TMP.LOAN_QUALITY,TMP.LOAN_STATUS) AS UUID
    ,TMP.DATA_DATE          
    ,TMP.AREA_NO_ID          
    ,TMP.DATA_SRC_ORG       
    ,TMP.DATA_ORG_CODE      
    ,TMP.CUSTOMER_TYPE      
    ,TMP.CUSTOMER_ID        
    ,TMP.LPIC           
    ,TMP.REGIST_ADD_CODE    
    ,TMP.ENTERPRISE_INVESTOR
    ,TMP.ENTERPRISE_SCALE   
    ,TMP.LOAN_IOUS_CODE     
    ,TMP.PRODUCT_CATEGORY   
    ,TMP.LOANS_ACTUALLY     
    ,TMP.LENDING_DATE       
    ,TMP.LOAN_DUE_DATE      
    ,TMP.ROLL_OVER_DATE     
    ,TMP.TERM     
    ,TMP.LOAN_CUR_TYPE      
    ,TMP.LOAN_BALANCE       
    ,TMP.RATE_FIXED_FLOAT   
    ,TMP.RATE_LEVEL         
    ,TMP.GUARANTY_STYLE     
    ,TMP.LOAN_QUALITY       
    ,TMP.LOAN_STATUS        
FROM(
    SELECT
         ROW_NUMBER() OVER(PARTITION BY LAB.DATA_DATE,ORG.DATA_ORG_AREA,LAB.DATA_SRC_ORG,LAB.DATA_ORG_CODE,LAB.CUSTOMER_TYPE,LAB.CUSTOMER_ID,LAB.LPIC,LAB.REGIST_ADD_CODE,LAB.ENTERPRISE_INVESTOR,LAB.ENTERPRISE_SCALE,LAB.LOAN_IOUS_CODE,LAB.PRODUCT_CATEGORY,LAB.LOANS_ACTUALLY,LAB.LENDING_DATE,LAB.LOAN_DUE_DATE,LAB.ROLL_OVER_DATE,LAB.LOAN_CUR_TYPE,LAB.LOAN_BALANCE,LAB.RATE_FIXED_FLOAT,LAB.RATE_LEVEL,LAB.GUARANTY_STYLE,LAB.LOAN_QUALITY,LAB.LOAN_STATUS) AS RN
        ,LAB.DATA_DATE          
        ,COALESCE(ORG.DATA_ORG_AREA,'999999') AS AREA_NO_ID
        ,LAB.DATA_SRC_ORG       
        ,LAB.DATA_ORG_CODE      
        ,LAB.CUSTOMER_TYPE      
        ,LAB.CUSTOMER_ID        
        ,LAB.LPIC           
        ,LAB.REGIST_ADD_CODE    
        ,LAB.ENTERPRISE_INVESTOR
        ,LAB.ENTERPRISE_SCALE   
        ,LAB.LOAN_IOUS_CODE     
        ,LAB.PRODUCT_CATEGORY   
        ,LAB.LOANS_ACTUALLY     
        ,LAB.LENDING_DATE       
        ,LAB.LOAN_DUE_DATE      
        ,LAB.ROLL_OVER_DATE     
        ,CEIL(MONTHS_BETWEEN(LAB.LOAN_DUE_DATE,LAB.LENDING_DATE)) AS TERM
        ,LAB.LOAN_CUR_TYPE      
        ,LAB.LOAN_BALANCE       
        ,LAB.RATE_FIXED_FLOAT   
        ,LAB.RATE_LEVEL         
        ,LAB.GUARANTY_STYLE     
        ,LAB.LOAN_QUALITY       
        ,LAB.LOAN_STATUS        
    FROM ODS.LOAB LAB LEFT JOIN DMCODE.T_ORG_MANAGE ORG ON LAB.DATA_ORG_CODE = ORG.DATA_ORG_ID
    WHERE LAB.DATA_DATE IN('#V_DATA_DATE#','#V_LAST_1M_END_DATE#','#V_LAST_2M_END_DATE#')
)TMP
;
